package com.erp.order.purchasing.dao.impl;

import com.erp.hr.base.dao.impl.BaseDaoImpl;
import com.erp.hr.base.util.CommonUtils;
import com.erp.order.purchasing.dao.InquiryListDao;
import com.erp.order.purchasing.model.*;
import org.apache.commons.lang.StringUtils;
import org.redragon.framework.hibernate.DaoSupport;
import org.redragon.framework.hibernate.model.Pages;
import org.redragon.framework.util.DaoCRUDUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 询价单 持久实现层
 * @Author: syz
 * @blame syz
 * @CreateTime: 2021/4/6
 */
@Repository
@Transactional
public class InquiryListDaoImpl extends BaseDaoImpl<InquiryList> implements InquiryListDao {

    /**
     * dao支持
     */
    @Autowired
    private DaoSupport daoSupport;


    /**
     * 获取列表分页数据
     * @param s 数据验证sql
     * @param pages 分页对象
     * @param inquiryList 查询条件对象
     * @return 列表分页数据
     */
    @Override
    public List<InquiryList> getDataObjectsForDataAuth(String s, Pages pages, InquiryList inquiryList) {
        String sql =
                "SELECT " +
                    "il.* " +
                "FROM " +
                    "inquiry_list il " +
                "WHERE " +
                    "il.create_user = :user ";
        Map<String, Object> args = new HashMap<>();
        args.put("user", inquiryList.getCreateUser());
        if (StringUtils.isNotBlank(inquiryList.getCode())) {
            inquiryList.setCode("%" + inquiryList.getCode() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(inquiryList, "code", "and il.", args);
        }
        if (StringUtils.isNotBlank(inquiryList.getTitle())) {
            inquiryList.setTitle("%" + inquiryList.getTitle() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(inquiryList, "title", "and il.", args);
        }
        sql += DaoCRUDUtil.getSQLSearch(inquiryList, "procurementType", "and il.", args);
        sql += DaoCRUDUtil.getSQLSearch(inquiryList, "status", "and il.", args);
        sql +=
                " AND " +
                    "il.is_del = '0' " +
                "ORDER BY " +
                    "il.update_time DESC";
        daoSupport.validateDataObject(inquiryList);
        return daoSupport.getDataSqlByPage(
                sql,
                new HashMap<String, Class<?>>() {{
                    put("il", InquiryList.class);
                }},
                args,
                pages);
    }

    /**
     * 根据id查询对象
     * @param id 对象id
     * @return 查询结果
     */
    @Override
    public InquiryList getDataObject(String id) {
        List<InquiryList> result = daoSupport.selectDataSql(
                "SELECT " +
                    "il.* " +
                "FROM " +
                    "inquiry_list il " +
                "WHERE " +
                    "il.id = :id",
                new HashMap<String, Class<?>>(){{
                    put("il", InquiryList.class);
                }},
                new HashMap<String, Object>(){{
                    put("id", id);
                }});
        return result == null || result.isEmpty() ? null : result.get(0);
    }

    @Override
    public InquiryList getDataObject(int id) {
        List<InquiryList> result = daoSupport.selectDataSql(
                "SELECT " +
                        "il.* " +
                    "FROM " +
                        "inquiry_list il " +
                    "WHERE " +
                        "il.id = :id",
                new HashMap<String, Class<?>>(){{
                    put("il", InquiryList.class);
                }},
                new HashMap<String, Object>(){{
                    put("id", id);
                }});
        return result == null || result.isEmpty() ? null : result.get(0);
    }

    @Override
    public List<InquiryList> getDataObjects(InquiryList inquiryList) {
        return null;
    }

    /**
     * 根据询价编码获取对象
     * @param code 询价编码
     * @return 询价单对象
     */
    @Override
    public InquiryList getByCode(String code) {
        List<InquiryList> result = daoSupport.selectDataSql(
                "SELECT " +
                        "il.* " +
                    "FROM " +
                        "inquiry_list il " +
                    "WHERE " +
                        "il.code = :code",
                new HashMap<String, Class<?>>(){{
                    put("il", InquiryList.class);
                }},
                new HashMap<String, Object>(){{
                    put("code", code);
                }});
        return result == null || result.isEmpty() ? null : result.get(0);
    }

    /**
     * 根据1688id获取对象
     * @param id 1688id
     * @return 询价单对象
     */
    @Override
    public InquiryList getBy1688Id(String id) {
        List<InquiryList> result = daoSupport.selectDataSql(
                "SELECT " +
                        "il.* " +
                    "FROM " +
                        "inquiry_list il " +
                    "WHERE " +
                        "il.other_id = :id",
                new HashMap<String, Class<?>>(){{
                    put("il", InquiryList.class);
                }},
                new HashMap<String, Object>(){{
                    put("id", id);
                }});
        return result == null || result.isEmpty() ? null : result.get(0);
    }

    /**
     * 获取时间范围内的创建数量
     * @return 时间范围内的申请数量
     */
    @Override
    public Integer getCreateCount() {
        String sql =
                "SELECT " +
                    "il.* " +
                "FROM " +
                    "inquiry_list il " +
                "WHERE " +
                    "il.is_del = :isDel " +
                "AND " +
                    "DATE_FORMAT(il.create_time, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')";
        Map<String, Object> args = new HashMap<>();
        args.put("isDel", CommonUtils.IS_DEL_NO);
        Map<String, Class<?>> entity = new HashMap<>();
        entity.put("il", InquiryList.class);
        List<InquiryList> result = daoSupport.selectDataSql(sql, entity, args);
        return result == null ? 0 : result.size();
    }

    @Override
    public List<InquiryListR> getMdVendorInquiryList(Pages pages, InquiryListR inquiryListR, String username) {

        String sql="SELECT qp.num AS num," +
                   "il.*," +
                   "DATEDIFF(il.quotation_closing_ate,CURDATE())AS days " +
                   "FROM inquiry_list il " +
                   "LEFT JOIN inquiry_list_supplier ils " +
                   "ON il.id=ils.inquiry_list_id " +
                   "LEFT JOIN " +
                "  (SELECT COUNT(*) AS num ,inquiry_list_id AS qpid FROM quoted_price WHERE STATUS='1' GROUP BY inquiry_list_id) qp " +
                "   ON il.id=qp.qpid  " +
                   "WHERE ils.supplier_id=(SELECT m.vendor_id FROM md_vendor m WHERE m.credit_code= :username) ";
        Map<String, Object> args = new HashMap<String, Object>();

        if (StringUtils.isNotBlank(inquiryListR.getCode())) {
            inquiryListR.setCode("%" + inquiryListR.getCode() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(inquiryListR, "code", "and il.", args);
        }
        if (StringUtils.isNotBlank(inquiryListR.getTitle())) {
            inquiryListR.setTitle("%" + inquiryListR.getTitle() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(inquiryListR, "title", "and il.", args);
        }
        sql = sql + DaoCRUDUtil.getSQLSearch(inquiryListR, "procurementType", "and il.", args);
        sql = sql + " order by il.begin_date desc";

        args.put("username", username);
        List<InquiryListR> result = daoSupport.getDataSqlByPage(sql, InquiryListR.class,args,pages);
        //List<InquiryListR> result = daoSupport.selectDataSql(sql,InquiryListR.class,args);
        return result;
    }

    @Override
    public InquiryList getDataById(int id) {
        String sql = "SELECT il.* FROM inquiry_list il WHERE il.id = :id";
        Map<String, Object> args = new HashMap<>();
        args.put("id", id);
        Map<String, Class<?>> entity = new HashMap<>();
        entity.put("il", InquiryList.class);
        List<InquiryList> result = daoSupport.selectDataSql(sql, entity, args);
        return null;
    }

    @Override
    public List<InquiryListRS> getQuotedLists(Pages pages, InquiryListRS inquiryListR, String username) {
        String sql="SELECT q.id AS qid,q.status AS qstatus,q.total,v.vendor_name,v.credit_code,i.*," +
                "DATEDIFF(i.quotation_closing_ate,CURDATE())AS days" +
                " FROM quoted_price q LEFT JOIN inquiry_list i" +
                " ON q.inquiry_list_id=i.id" +
                " LEFT JOIN md_vendor v ON q.vendor_id=v.vendor_id" +
                " WHERE v.vendor_id=(SELECT m.vendor_id FROM md_vendor m WHERE m.credit_code=:username)" +
                " AND q.status=1 ";
        Map<String, Object> args = new HashMap<String, Object>();

        if (StringUtils.isNotBlank(inquiryListR.getCode())) {
            inquiryListR.setCode("%" + inquiryListR.getCode() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(inquiryListR, "code", "and i.", args);
        }
        if (StringUtils.isNotBlank(inquiryListR.getTitle())) {
            inquiryListR.setTitle("%" + inquiryListR.getTitle() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(inquiryListR, "title", "and i.", args);
        }
        sql = sql + DaoCRUDUtil.getSQLSearch(inquiryListR, "procurementType", "and i.", args);

        sql = sql + " order by i.begin_date desc";
        args.put("username", username);
        List<InquiryListRS> result = daoSupport.getDataSqlByPage(sql, InquiryListRS.class,args,pages);
        return result;
    }

    /**
     * 根据询价单id获取已报价供应商集合
     * @param id 询价单id
     * @return 已报价供应商集合
     */
    @Override
    public List<InquiryListSupplier> getSupplierById(Long id) {
        return daoSupport.selectDataSql(
                "SELECT " +
                    "ils.* " +
                "FROM " +
                    "quoted_price qp " +
                "LEFT JOIN " +
                    "inquiry_list_supplier ils " +
                "ON " +
                    "qp.inquiry_list_id = ils.inquiry_list_id " +
                "AND " +
                    "qp.vendor_id = ils.supplier_id " +
                "WHERE " +
                    "qp.inquiry_list_id = :id " +
                "AND " +
                    "qp.status = '1' " +
                "ORDER BY " +
                    "qp.total",
                new HashMap<String, Class<?>>() {{
                    put("ils", InquiryListSupplier.class);
                }},
                new HashMap<String, Object>(){{
                    put("id", id);
                }});
    }

    /**
     * 根据询价单/询价物资id获取报价详情集合
     * @param inquiryListId 询价单id
     * @param inquiryListMaterialId 询价物资id
     * @return 报价详情集合
     */
    @Override
    public List<QuotedPriceDetail> getQuoteMaterialList(Long inquiryListId, Long inquiryListMaterialId) {
        return daoSupport.selectDataSql(
                "SELECT " +
                    "qpd.* " +
                "FROM " +
                    "quoted_price_detail qpd " +
                "WHERE " +
                    "qpd.inquiry_list_id = :il " +
                "AND " +
                    "qpd.material_id = :ilm " +
                "ORDER BY " +
                    "qpd.price",
                new HashMap<String, Class<?>>(){{
                    put("qpd", QuotedPriceDetail.class);
                }},
                new HashMap<String, Object>(){{
                    put("il", inquiryListId);
                    put("ilm", inquiryListMaterialId);
                }});
    }

    @Override
    public List<QuotePriceInfo> getPriceInfoById(Long id) {
        String sql="SELECT d.price as dprice," +
                "d.is_tax," +
                "d.tax_rate," +
                "d.totla," +
                "d.another_name," +
                "d.supplier_remark," +
                "d.all_respond," +
                "d.respond_remark," +
                "m.* FROM " +
                "quoted_price_detail d " +
                "LEFT JOIN quoted_price q ON d.quoted_price_id=q.id  " +
                "LEFT JOIN inquiry_list_material m ON d.material_id=m.id " +
                "where q.id= :id " +
                "order by m.id";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("id", id);
        return daoSupport.selectDataSql(sql,QuotePriceInfo.class,args);
    }

}